
import requests
import openpyxl
from openpyxl import load_workbook
import time

import json
from urllib.parse import urlparse

def get_cnblogs_fields(url, fields=["postStats.viewCount", "postStats.feedbackCount"]):
    """
    获取博客园(CNBlogs)文章的多个指定字段数据
    :param url: 博客园文章URL，例如 "https://www.cnblogs.com/huaweiyun/p/18735823"
    :param fields: 要提取的字段名列表，默认为["viewCount", "feedbackCount"]
                  支持嵌套字段，如"postStats.viewCount"或"postStats/diggCount"
    :return: 字典包含请求的字段和对应的值，或错误信息
    """
    try:
        # 从URL中提取文章ID和博客名
        parsed_url = urlparse(url)
        path_parts = parsed_url.path.split('/')
            
        post_id = path_parts[-1]
        if not post_id.isdigit():
            return {"error": "URL格式不正确，文章ID不是数字"}
        
        # 博客园API接口
        api_url = f"https://www.cnblogs.com/huaweiyun/ajax/post-accessories?postId={post_id}"
        
        # 请求头
        headers = {
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.93 Safari/537.36",
            "Referer": url,
            "Accept": "application/json"
        }
        
        # 发送GET请求
        response = requests.get(api_url, headers=headers)
        response.raise_for_status()
        
        # 解析响应
        data = response.json()
        
        # 提取所有请求的字段
        result = {}
        for field in fields:
            # 处理嵌套字段
            keys = field.replace('/', '.').split('.')
            value = data
            try:
                for key in keys:
                    if isinstance(value, dict) and key in value:
                        value = value[key]
                    else:
                        value = None
                        break
                result[field] = value if value is not None else "N/A"
            except Exception:
                result[field] = "N/A"
        
        # 检查是否获取到了有效数据
        if all(v == "N/A" for v in result.values()):
            return {"error": "响应中未找到请求的字段"}
            
        return result
            
    except requests.exceptions.RequestException as e:
        return {"error": f"请求失败: {str(e)}"}
    except json.JSONDecodeError:
        return {"error": "响应解析失败"}
    except Exception as e:
        return {"error": f"发生错误: {str(e)}"}

def process_excel_file(
    file_path: str,
    sheet_name: str = None,  # 默认当前活动页签
    url_column: str = "V",   # URL所在列
    result_column_views: str = "W", # 浏览量结果写入列
    result_column_feedback : str = "AB" # 评论量结果写入列
) -> None:
    """
    从Excel文件的指定页签和列中读取URL，根据URL类型自动选择提取方法，
    统计阅读数并保存结果。

    Args:
        file_path (str): 输入Excel文件路径
        sheet_name (str, optional): 页签名称。默认为当前活动页签
        url_column (str, optional): URL列名（如 "D"）。默认为 "D"
        result_column_views (str, optional): 浏览量结果写入列名（如 "E"）。默认为 "E"
        result_column_feedback (str, optional): 评论量结果写入列名（如 "F"）。默认为 "F"
    """
    # 加载Excel文件
    wb = load_workbook(file_path)
    ws = wb[sheet_name] if sheet_name else wb.active

    # 将列名转换为列索引
    url_col_idx = openpyxl.utils.column_index_from_string(url_column)
    result_col_views_idx = openpyxl.utils.column_index_from_string(result_column_views)
    result_col_feedback_idx = openpyxl.utils.column_index_from_string(result_column_feedback)

    # 获取URL列的所有行
    for row_idx, row in enumerate(ws.iter_rows(min_col=url_col_idx, max_col=url_col_idx, values_only=True), start=1):
        url = row[0]
        if not isinstance(url, str) or not url.strip().startswith("http"):
            continue

        print(f"处理URL[{row_idx}]: {url}","获取默认字段(postStats.viewCount和postStats.feedbackCount): ")
        
        try:
            stats = get_cnblogs_fields(url)
            if "error" in stats:
                print(f"错误: {stats['error']}")
            else:
                print(f"阅读量: {stats['postStats.viewCount']}, 评论数: {stats['postStats.feedbackCount']}")
            ws.cell(row=row_idx, column=result_col_views_idx, value=stats['postStats.viewCount'])
            ws.cell(row=row_idx, column=result_col_feedback_idx, value=stats['postStats.feedbackCount'])
            time.sleep(1)  # 请求间隔
            print(f"已更新行 {row_idx} 的数据 URL: {url}, viewCount: {stats['postStats.viewCount']}, feedbackCoun: {stats['postStats.feedbackCount']}")
            
        except Exception as e:
            print(f"处理失败: {str(e)}")
            ws.cell(row=row_idx, column=result_col_views_idx, value=f"Error: {str(e)}")
            ws.cell(row=row_idx, column=result_col_feedback_idx, value=f"Error: {str(e)}")

    # 保存结果
    wb.save(file_path)
    print(f"处理完成，结果已保存到: {file_path}")
    
# 示例使用
if __name__ == "__main__":
    process_excel_file(
        file_path="C:/Users/qwx1425249/Desktop/data/开发者空间案例内外媒运营数据看板.xlsx",
        sheet_name="案例发布计划",  # 指定页签名称（可选）
        url_column="V",
        result_column_views="W",
        result_column_feedback ="AB"
    )
